﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LinqAndLambda.Modules
{
    public class Join关键字
    {
        public static void mian()
        {
            //左链接
            var products = from p in GetProducts()
                           join c in GetCategories() on p.CategoryId equals c.CategoryId
                           select new { p.ProductName, c.CategoryName };

            //组连接  :分类Id 分类名称 商品数量
            var products1 = from c in GetCategories()
                            join p in GetProducts() on c.CategoryId equals p.CategoryId into ps
                            select new
                            {
                                c.CategoryId,
                                c.CategoryName,
                                count = ps.Count()
                            };

            //左外连接 : 内连接+没有匹配到的数据
            //商品名，分类名称
            var products2 = from p in GetProducts()
                            join c in GetCategories() on p.CategoryId equals c.CategoryId into cs
                            from c2 in cs.DefaultIfEmpty(new Category() { CategoryName = "无" })
                            select new { p.ProductName, c2.CategoryName };
            //from c2 in cs.DefaultIfEmpty()
            //select new { p.ProductName, CategoryName = c2 == null ? "" : c2.CategoryName };

            var products3 = from c in GetCategories()
                            join p in GetProducts() on c.CategoryId equals p.CategoryId into ps
                            from c2 in ps.DefaultIfEmpty(new Product() { ProductName = "没有商品" })
                            select new { c.CategoryName, c2.ProductName };



            foreach (var item in products)
            {
                Console.WriteLine($"商品名:{item.ProductName},分类名:{item.CategoryName}");
            }
            Console.WriteLine("--------------------------------------------------------------");

            foreach (var item in products1)
            {
                Console.WriteLine($"分类Id:{item.CategoryId},分类名称:{item.CategoryName},商品数量:{item.count}");
            }
            Console.WriteLine("--------------------------------------------------------------");

            foreach (var item in products2)
            {
                Console.WriteLine($"商品名:{item.ProductName},分类名称:{item.CategoryName}");
            }

            Console.WriteLine("--------------------------------------------------------------");

            foreach (var item in products3)
            {
                Console.WriteLine($"分类名称:{item.CategoryName},商品名称:{item.ProductName}");
            }
        }

        public static List<Category> GetCategories()
        {
            List<Category> list = new List<Category>(){
            new Category(){ CategoryId=1, CategoryName="服装" },
            new Category(){ CategoryId=2, CategoryName="食品" },
            new Category(){ CategoryId=3, CategoryName="办公" },
            new Category(){ CategoryId=4, CategoryName="饮料" }
        };
            return list;
        }

        public static List<Product> GetProducts()
        {
            List<Product> list = new List<Product>(){
            new Product(){ ProductId=1,  ProductName="雪中飞羽绒服" , Price=998, Storage=588, CategoryId=1},
            new Product(){ ProductId=2, ProductName="安踏鞋子" , Price=198, Storage=288, CategoryId=1},
            new Product(){ ProductId=3, ProductName="短袖", Price=95, Storage=88, CategoryId=1 },
            new Product(){ ProductId=4, ProductName="旺旺雪饼" ,Price=16.5, Storage=18, CategoryId=2  },
            new Product(){ ProductId=5, ProductName="汇源果汁" ,Price=6, Storage=12, CategoryId=2 },
            new Product(){ ProductId=6, ProductName="英雄钢笔"  ,Price=12.5, Storage=59, CategoryId=3 },
            new Product(){ ProductId=7, ProductName="小米音响"  ,Price=99, Storage=10, CategoryId=7 }
        };
            return list;
        }
    }
}
